﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.IO;
using Lucene.Net.Documents;
using CommonSearchModule;
using System.Data.SqlClient;
using Lucene.Net.Analysis.Standard;
using Lucene.Net.Index;
using System.Diagnostics;
using System.Runtime.InteropServices;
using System.Text.RegularExpressions;
using System.Data;
namespace CreateIndexFile
{
    public class SearchRes_ItemIndexFile : IndexFileBase
    {
        public SearchRes_ItemIndexFile()
            : base(ConfigurationManager.AppSettings["Res_Item_IndexDocumentsPath"])
        { }

        #region Public Methods

        public override void Create()
        {
            //创建分析器
            Stopwatch watch = new Stopwatch();
            watch.Start();
            StringBuilder sb;
            #region 点播类

            using (SqlConnection conn = new SqlConnection(connString))
            {
                sb = new StringBuilder();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                sb.Append(@"  /*
  功能：查询包与分类，属性值的列表
  作用：将属性值显示在一行，用｜分隔
  用途：可以用在lucene系统上
  */
  DECLARE @id INT
  DECLARE @itemname NVARCHAR(250)
  DECLARE @info NVARCHAR(4000)
  DECLARE @cateid INT
  DECLARE @categoryname NVARCHAR(20)
  DECLARE @packageTime DATETIME
  DECLARE @classHour INT
  DECLARE @Price DECIMAL(9, 2)
 --课时数
  DECLARE @sales INT
  DECLARE @PID INT
 --已学人数
  DECLARE @picture NVARCHAR(500)
 --包的图像

--包名称
  DECLARE @PropertyID NVARCHAR(200)
 --属性值
  DECLARE @R_PropertyIDS NVARCHAR(500)
  --价格列表
  DECLARE @priceS VARCHAR(500)
  DECLARE @displayprice VARCHAR(30)
  DECLARE @tab TABLE
    (
      PrimaryKey VARCHAR(200) ,
      ItemID INT ,
      ItemName NVARCHAR(500) ,
      PropertyName NVARCHAR(500) ,
      Info NVARCHAR(4000) ,
      CateID INT ,
      CategoryName NVARCHAR(20) ,
      PropertyID INT ,
      PackageTime DATETIME ,
      Price DECIMAL(9, 2) ,
      Prices VARCHAR(500) ,
      ClassHour INT ,
      Sales INT ,
      picture NVARCHAR(500)
    )

  DECLARE cursor2 CURSOR
  FOR
    SELECT distinct 
            ( a.ID ) ,
            a.Name ,
            c.Name ,
            d.ExtendInfo ,
            a.PackageTime ,
            ISNULL(a.ClassHour, 0) AS ClassHour ,
            ISNULL(a.Sales, 0) + ISNULL(a.SaleRadix, 0) AS Sales ,
            a.picture ,
            Price = ( SELECT    MAX(maxprice)
                      FROM      dbo.Res_ItemPrice
                      WHERE     ItemID = a.ID and (platformID & 1)>0
                    )
    FROM    dbo.Res_Item a
            INNER JOIN dbo.Res_ItemCategory b ON a.ID = b.ItemID
                                                 AND Status = 1
            INNER JOIN dbo.Common_Category c ON b.CategoryID = c.ID
            LEFT JOIN dbo.Res_Item_ExtendFiled d ON a.ID = d.ItemID
                                                     AND d.ExtendFieldID = 3
            INNER JOIN dbo.Res_ItemPrice p on a.ID=p.itemID
    WHERE   a.status = 1 and (b.platformid & 1)>0  and (p.platformid & 1)>0 and p.status=1 and (a.itemtype=0 or a.itemtype=1)  

  OPEN cursor2
  FETCH NEXT FROM cursor2 INTO @id, @itemname, @categoryname, @info,
    @packageTime, @classHour, @sales, @picture, @price
   --将游标向下移1行，获取的数据放入之前定义的变量@id,@name中
  WHILE @@fetch_status = 0 
    BEGIN
    
    
    
          --属性值集合
        SET @R_PropertyIDS = ''
        DECLARE cursor4 CURSOR --取出这个资源的属值列表
        FOR
            SELECT  DISTINCT
                    ( b.Name ),b.PID
            FROM    dbo.Res_ItemCategory a
                    INNER JOIN dbo.Common_BasePropValue b ON a.ValueID = b.VID
            WHERE   a.ItemID = @id
                    AND ( b.PID = 1
                          OR b.PID = 2
                        )  ORDER BY b.PID 
        OPEN cursor4
    --打开游标
        FETCH NEXT FROM cursor4 INTO @PropertyID,@PID

        WHILE @@fetch_status = 0           --判断是否成功获取数据
            BEGIN
                SET @R_PropertyIDS = @R_PropertyIDS + @PropertyID + '|'
                FETCH NEXT FROM cursor4 INTO @PropertyID,@PID
            END
        CLOSE cursor4
                   --关闭游标
        DEALLOCATE cursor4


          --价格集合
        SET @priceS = ''
        DECLARE cursor4 CURSOR --取出这个资源的属值列表
        FOR
            SELECT  RTRIM(LTRIM(CONVERT(VARCHAR(10), a.MaxPrice))) + '-'
                    + RTRIM(LTRIM(CONVERT(VARCHAR(5), a.Interval))) + '-'
                    + a.Type AS aa
            FROM    dbo.Res_ItemPrice a
            WHERE   a.ItemID = @id
        OPEN cursor4
    --打开游标
        FETCH NEXT FROM cursor4 INTO @displayprice

        WHILE @@fetch_status = 0           --判断是否成功获取数据
            BEGIN
                SET @priceS = @priceS + @displayprice + '|'
                FETCH NEXT FROM cursor4 INTO @displayprice
            END
        CLOSE cursor4
                   --关闭游标
        DEALLOCATE cursor4




        INSERT  INTO @tab
                ( PrimaryKey ,
                  ItemID ,
                  ItemName ,
                  PropertyName ,
                  CategoryName ,
                  Info ,
                  PackageTime ,
                  Prices ,
                  ClassHour ,
                  Sales ,
                  Picture ,
                  Price
                )
        VALUES  ( 'Res_Item' + LTRIM(STR(@ID)) ,
                  @ID ,
                  @itemname ,
                  @R_PropertyIDS ,
                  @categoryname ,
                  @info ,
                  @packageTime ,
                  @prices ,
                  @classHour ,
                  @sales ,
                  @picture ,
                  @price
                )

        FETCH NEXT FROM cursor2 INTO @id, @itemname, @categoryname, @info,
            @packageTime, @classHour, @sales, @picture, @price
   --将游标向下移1行
    END
  CLOSE cursor2
  DEALLOCATE cursor2
  SELECT    * ,
            SearchUrlType = '1'
  FROM      @tab
  ORDER BY  PackageTime DESC  
");
                comm.CommandText = sb.ToString();
                conn.Open();
                comm.CommandTimeout = 0;
                SqlDataReader reader = comm.ExecuteReader(CommandBehavior.CloseConnection);
                int rowCount = 1;
                try
                {
                    while (reader.Read())
                    {
                        Search_Entity entity = new Search_Entity();
                        entity.PrimaryKey = reader["PrimaryKey"].ToString();
                        entity.ID = reader["ItemID"].ToString();
                        entity.Name = reader["ItemName"].ToString();
                        entity.Info = Regex.Replace(reader["Info"].ToString(), @"<[^>]*>", string.Empty, RegexOptions.IgnoreCase);
                        entity.CategoryName = reader["CategoryName"].ToString();
                        entity.PackageTime = reader["PackageTime"].ToString();
                        entity.PropertyName = reader["PropertyName"].ToString();
                        entity.SearchUrlType = reader["SearchUrlType"].ToString();
                        entity.Price = Convert.ToDouble(reader["Price"]);
                        entity.ExtentionField1 = reader["ClassHour"].ToString();
                        entity.ExtentionField2 = reader["Sales"].ToString();
                        entity.ExtentionField3 = reader["Picture"].ToString();
                        entity.ExtentionField4 = reader["Prices"].ToString();
                        //添加到索引
                        Document doc = InitDocument(entity);
                        writer.AddDocument(doc);

                        Console.SetCursorPosition(0, 0);
                        Console.WriteLine("执行次数：" + rowCount++.ToString());
                        Console.WriteLine("执行时间：" + watch.Elapsed.ToString());
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception(conn.ConnectionTimeout + ex.Message);
                }
                finally
                {
                    reader.Close();
                    reader.Dispose();

                }
            }

            #endregion 点播类
            #region 直播类


            //直播课程
            using (SqlConnection conn = new SqlConnection(connString_LiveCourse))
            {
                #region 小班

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = @"SELECT 'View_LiveCourseList小班'+ltrim(str(a.id))  as PrimaryKey,a.id AS ItemID ,
        a.Title AS Name ,
        a.Info AS Info ,
        '小班' AS CategoryName ,
        b.Name + '|' + c.NAME AS PropertyName ,
        a.StartDate AS PackageTime,
SearchUrlType='4'
FROM    TsingDaClass.dbo.View_LiveCourseList a
        INNER JOIN EEE114.dbo.Common_BasePropValue b ON a.GradeID = b.VID
        INNER JOIN EEE114.dbo.Common_BasePropValue c ON a.SubjectID = c.VID
WHERE   a.ClassTypeID = 177  and  a.IsValid=1 and a.IsDelete=0 order by PackageTime desc";
                conn.Open();
                SqlDataReader reader = comm.ExecuteReader();

                int rowCount = 1;
                try
                {
                    while (reader.Read())
                    {
                        Search_Entity entity = new Search_Entity();
                        entity.PrimaryKey = reader["PrimaryKey"].ToString();
                        entity.ID = reader["ItemID"].ToString();
                        entity.Name = reader["Name"].ToString();
                        entity.Info = Regex.Replace(reader["Info"].ToString(), @"<[^>]*>", string.Empty, RegexOptions.IgnoreCase);
                        entity.CategoryName = reader["CategoryName"].ToString();
                        entity.PackageTime = reader["PackageTime"].ToString();
                        entity.PropertyName = reader["PropertyName"].ToString();
                        entity.SearchUrlType = reader["SearchUrlType"].ToString();
                        //添加到索引
                        Document doc = InitDocument(entity);
                        writer.AddDocument(doc);

                        Console.SetCursorPosition(0, 0);
                        Console.WriteLine("执行次数：" + rowCount++.ToString());
                        Console.WriteLine("执行时间：" + watch.Elapsed.ToString());
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.ReadKey();
                }
                finally
                {
                    reader.Close();
                }

                #endregion 小班

                #region 名师

                comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = @"SELECT  'View_LiveCourseList名师'+ltrim(str(a.id))  as PrimaryKey, a.id AS ItemID ,
        a.Title AS Name ,
        a.Info AS Info ,
        '名师大讲堂' AS CategoryName ,
        b.Name + '|' + c.NAME AS PropertyName ,
        a.StartDate AS PackageTime,
SearchUrlType='3'
FROM    TsingDaClass.dbo.View_LiveCourseList a
        INNER JOIN EEE114.dbo.Common_BasePropValue b ON a.GradeID = b.VID
        INNER JOIN EEE114.dbo.Common_BasePropValue c ON a.SubjectID = c.VID
WHERE   a.ClassTypeID = 178 and  a.IsValid=1 and a.IsDelete=0 order by PackageTime desc";
                reader = comm.ExecuteReader();

                rowCount = 1;
                try
                {
                    while (reader.Read())
                    {
                        Search_Entity entity = new Search_Entity();
                        entity.PrimaryKey = reader["PrimaryKey"].ToString();
                        entity.ID = reader["ItemID"].ToString();
                        entity.Name = reader["Name"].ToString();
                        entity.Info = Regex.Replace(reader["Info"].ToString(), @"<[^>]*>", string.Empty, RegexOptions.IgnoreCase);
                        entity.CategoryName = reader["CategoryName"].ToString();
                        entity.PackageTime = reader["PackageTime"].ToString();
                        entity.PropertyName = reader["PropertyName"].ToString();
                        entity.SearchUrlType = reader["SearchUrlType"].ToString();
                        //添加到索引
                        Document doc = InitDocument(entity);
                        writer.AddDocument(doc);

                        Console.SetCursorPosition(0, 0);
                        Console.WriteLine("执行次数：" + rowCount++.ToString());
                        Console.WriteLine("执行时间：" + watch.Elapsed.ToString());
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.ReadKey();
                }
                finally
                {
                    reader.Close();
                }

                #endregion 名师
            }


            #endregion 直播类
            writer.Optimize();
            writer.Close();

            TimeSpan _RunTime = new TimeSpan(DateTime.Now.Ticks);
            TimeSpan _RunTime2 = new TimeSpan(DateTime.Now.AddDays(-2).Ticks);
            TimeSpan ss = _RunTime.Subtract(_RunTime2);
            Console.WriteLine(ss.Hours);

            SetCreatePath();
        }

        #endregion Public Methods
    }
}
